CREATE TABLE [dbo].[Name]
(
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_ID] DEFAULT (''),
[ORG_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_ORG_CODE] DEFAULT (''),
[MEMBER_TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_MEMBER_TYPE] DEFAULT (''),
[CATEGORY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_CATEGORY] DEFAULT (''),
[STATUS] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_STATUS] DEFAULT (''),
[MAJOR_KEY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_MAJOR_KEY] DEFAULT (''),
[CO_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_CO_ID] DEFAULT (''),
[LAST_FIRST] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_LAST_FIRST] DEFAULT (''),
[COMPANY_SORT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_COMPANY_SORT] DEFAULT (''),
[BT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_BT_ID] DEFAULT (''),
[DUP_MATCH_KEY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_DUP_MATCH_KEY] DEFAULT (''),
[FULL_NAME] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FULL_NAME] DEFAULT (''),
[TITLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_TITLE] DEFAULT (''),
[COMPANY] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_COMPANY] DEFAULT (''),
[FULL_ADDRESS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FULL_ADDRESS] DEFAULT (''),
[PREFIX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_PREFIX] DEFAULT (''),
[FIRST_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FIRST_NAME] DEFAULT (''),
[MIDDLE_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_MIDDLE_NAME] DEFAULT (''),
[LAST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_LAST_NAME] DEFAULT (''),
[SUFFIX] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_SUFFIX] DEFAULT (''),
[DESIGNATION] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_DESIGNATION] DEFAULT (''),
[INFORMAL] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_INFORMAL] DEFAULT (''),
[WORK_PHONE] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_WORK_PHONE] DEFAULT (''),
[HOME_PHONE] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_HOME_PHONE] DEFAULT (''),
[FAX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FAX] DEFAULT (''),
[TOLL_FREE] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_TOLL_FREE] DEFAULT (''),
[CITY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_CITY] DEFAULT (''),
[STATE_PROVINCE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_STATE_PROVINCE] DEFAULT (''),
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_ZIP] DEFAULT (''),
[COUNTRY] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_COUNTRY] DEFAULT (''),
[MAIL_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_MAIL_CODE] DEFAULT (''),
[CRRT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_CRRT] DEFAULT (''),
[BAR_CODE] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_BAR_CODE] DEFAULT (''),
[COUNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_COUNTY] DEFAULT (''),
[MAIL_ADDRESS_NUM] [int] NOT NULL CONSTRAINT [DF_Name_MAIL_ADDRESS_NUM] DEFAULT ((0)),
[BILL_ADDRESS_NUM] [int] NOT NULL CONSTRAINT [DF_Name_BILL_ADDRESS_NUM] DEFAULT ((0)),
[GENDER] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_GENDER] DEFAULT (''),
[BIRTH_DATE] [datetime] NULL,
[US_CONGRESS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_US_CONGRESS] DEFAULT (''),
[STATE_SENATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_STATE_SENATE] DEFAULT (''),
[STATE_HOUSE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_STATE_HOUSE] DEFAULT (''),
[SIC_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_SIC_CODE] DEFAULT (''),
[CHAPTER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_CHAPTER] DEFAULT (''),
[FUNCTIONAL_TITLE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FUNCTIONAL_TITLE] DEFAULT (''),
[CONTACT_RANK] [int] NOT NULL CONSTRAINT [DF_Name_CONTACT_RANK] DEFAULT ((0)),
[MEMBER_RECORD] [bit] NOT NULL CONSTRAINT [DF_Name_MEMBER_RECORD] DEFAULT ((0)),
[COMPANY_RECORD] [bit] NOT NULL CONSTRAINT [DF_Name_COMPANY_RECORD] DEFAULT ((0)),
[JOIN_DATE] [datetime] NULL,
[SOURCE_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_SOURCE_CODE] DEFAULT (''),
[PAID_THRU] [datetime] NULL,
[MEMBER_STATUS] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_MEMBER_STATUS] DEFAULT (''),
[MEMBER_STATUS_DATE] [datetime] NULL,
[PREVIOUS_MT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_PREVIOUS_MT] DEFAULT (''),
[MT_CHANGE_DATE] [datetime] NULL,
[CO_MEMBER_TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_CO_MEMBER_TYPE] DEFAULT (''),
[EXCLUDE_MAIL] [bit] NOT NULL CONSTRAINT [DF_Name_EXCLUDE_MAIL] DEFAULT ((0)),
[EXCLUDE_DIRECTORY] [bit] NOT NULL CONSTRAINT [DF_Name_EXCLUDE_DIRECTORY] DEFAULT ((0)),
[DATE_ADDED] [datetime] NULL,
[LAST_UPDATED] [datetime] NULL,
[UPDATED_BY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_UPDATED_BY] DEFAULT (''),
[INTENT_TO_EDIT] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_INTENT_TO_EDIT] DEFAULT (''),
[ADDRESS_NUM_1] [int] NOT NULL CONSTRAINT [DF_Name_ADDRESS_NUM_1] DEFAULT ((0)),
[ADDRESS_NUM_2] [int] NOT NULL CONSTRAINT [DF_Name_ADDRESS_NUM_2] DEFAULT ((0)),
[ADDRESS_NUM_3] [int] NOT NULL CONSTRAINT [DF_Name_ADDRESS_NUM_3] DEFAULT ((0)),
[EMAIL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_EMAIL] DEFAULT (''),
[WEBSITE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_WEBSITE] DEFAULT (''),
[SHIP_ADDRESS_NUM] [int] NOT NULL CONSTRAINT [DF_Name_SHIP_ADDRESS_NUM] DEFAULT ((0)),
[DISPLAY_CURRENCY] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_DISPLAY_CURRENCY] DEFAULT (''),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_Name_Delete]
   ON  [dbo].[Name]
   FOR DELETE
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @entityKey uniqueidentifier
    DECLARE @defaultOrganizationKey uniqueidentifier
    DECLARE @adminUserKey uniqueidentifier
    DECLARE @now datetime

    SELECT @entityKey = SystemEntityKey FROM SystemEntity WHERE SystemKeyword = 'Organization'
    SELECT @defaultOrganizationKey = OrganizationKey FROM OrganizationMain WHERE IsDefault = 1
    SELECT @adminUserKey = ParameterValue from SystemConfig
        WHERE ParameterName = 'Security.Token.AdministratorUserKey'
            and SystemEntityKey = @entityKey and OrganizationKey = @defaultOrganizationKey
    SET @now = getdate()

    -- Mark any associatd 'User' record as disabled
    UPDATE um
       SET [IsDisabled] = 1,
       um.[ContactMaster] = '',
       um.[UserId] = ''
      FROM deleted d INNER JOIN [dbo].[ContactMain] cm ON d.[ID] = cm.[SyncContactID]
                     INNER JOIN [dbo].[UserMain] um ON cm.[ContactKey] = um.[UserKey]

    -- Mark associated Contact as deleted
    UPDATE [ContactMain]
    SET [ContactStatusCode] = cs.[ContactStatusCode],
        [UpdatedByUserKey] = @adminUserKey,
        [UpdatedOn] = @now,
        [SyncContactID] = NULL  -- Have to remove SyncContactID since C/S can reuse IDs
    FROM deleted d INNER JOIN [ContactStatusRef] cs ON cs.[ContactStatusDesc] = 'Delete' AND cs.[IsSystem] = 1
    WHERE [ContactMain].[SyncContactID] = d.ID

    SET NOCOUNT OFF
END

GO


CREATE TRIGGER [dbo].[asi_Name_Insert_Update]
   ON  [dbo].[Name]
   FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON
    IF NOT EXISTS (SELECT 1
                     FROM inserted i
                          LEFT JOIN deleted d ON i.ID = d.ID
                          LEFT JOIN ContactMain c ON i.ID = c.SyncContactID
                    WHERE i.[LAST_FIRST] <> d.[LAST_FIRST] OR i.[COMPANY_RECORD] <> d.[COMPANY_RECORD] OR
                          i.[COMPANY] <> d.[COMPANY] OR i.[PREFIX] <> d.[PREFIX] OR
                          i.[SUFFIX] <> d.[SUFFIX] OR i.[BIRTH_DATE] <> d.[BIRTH_DATE] OR
                          i.[STATUS] <> d.[STATUS] OR i.[EXCLUDE_MAIL] <> d.[EXCLUDE_MAIL] OR
                          i.[FIRST_NAME] <> d.[FIRST_NAME] OR i.[MIDDLE_NAME] <> d.[MIDDLE_NAME] OR
                          i.[LAST_NAME] <> d.[LAST_NAME] OR i.[DESIGNATION] <> d.[DESIGNATION] OR
                          i.[INFORMAL] <> d.[INFORMAL] OR i.[GENDER] <> d.[GENDER] OR
                          i.[MEMBER_TYPE] <> d.[MEMBER_TYPE] OR i.[CO_ID] <> d.[CO_ID] OR
                          d.[ID] IS NULL OR c.SyncContactID IS NULL
                   ) AND (SELECT COUNT(1) FROM inserted) > 0
    BEGIN
        RETURN
    END

    DECLARE @defaultOrganizationKey uniqueidentifier
    DECLARE @adminUserKey uniqueidentifier
    DECLARE @accessKey uniqueidentifier
    DECLARE @adminRoleKey uniqueidentifier
    DECLARE @everyoneRoleKey uniqueidentifier
    DECLARE @instituteTypeKey uniqueidentifier
    DECLARE @groupTypeKey uniqueidentifier
    DECLARE @individualTypeKey uniqueidentifier
    DECLARE @systemEntityKey uniqueidentifier
    DECLARE @primaryGroupRoleKey uniqueidentifier
    DECLARE @primaryDefaultMemberStatusCode nvarchar(4)
    DECLARE @groupRoleKey uniqueidentifier
    DECLARE @defaultMemberStatusCode nvarchar(4)
    DECLARE @now datetime
    DECLARE @createdByUserKey uniqueidentifier
    DECLARE @updatedByUserId varchar(15)

    -- Get some constant values we need
    SELECT @defaultOrganizationKey = OrganizationKey FROM OrganizationMain WHERE IsDefault = 1

    SELECT @systemEntityKey = SystemEntityKey FROM SystemEntity
     WHERE IsDefault = 1 AND SystemKeyword = 'Organization'   

    SELECT @adminUserKey = ParameterValue FROM SystemConfig
     WHERE ParameterName = 'Security.Token.AdministratorUserKey'
       and SystemEntityKey = @systemEntityKey and OrganizationKey = @defaultOrganizationKey

    SELECT @accessKey = ParameterValue FROM SystemConfig
     WHERE ParameterName = 'Security.Token.EveryoneFullControlAccessKey'
            and SystemEntityKey = @systemEntityKey and OrganizationKey = @defaultOrganizationKey

    SELECT @adminRoleKey = ParameterValue FROM SystemConfig
     WHERE ParameterName = 'Security.Token.AdministratorsRoleKey'

    SELECT @everyoneRoleKey = ParameterValue FROM SystemConfig
     WHERE ParameterName = 'Security.Token.EveryoneRoleKey'

    SELECT @instituteTypeKey = ContactTypeKey, @groupTypeKey = GroupTypeKey FROM ContactTypeRef
     WHERE ContactTypeDesc = 'Company'

    SELECT @individualTypeKey = ContactTypeKey from ContactTypeRef where IsInstitute = 0

    SELECT @primaryGroupRoleKey = a.GroupRoleKey,
           @primaryDefaultMemberStatusCode = a.DefaultMemberStatusCode
      FROM GroupTypeRole a INNER JOIN GroupRoleRef b
        ON a.GroupRoleKey = b.GroupRoleKey
     WHERE b.GroupRoleDesc = 'Primary Contact'
       AND a.IsSystem = 1

    SELECT @groupRoleKey = GroupRoleKey,
           @defaultMemberStatusCode = DefaultMemberStatusCode
      FROM GroupTypeRole
     WHERE GroupTypeKey = @groupTypeKey
       AND IsDefault = 1

    SET    @now = getdate()

    -- Get createdBy/updatedBy user key
    SELECT @updatedByUserId = MIN(REPLACE(UPDATED_BY, '-IBO', '')) FROM inserted WHERE LEN(REPLACE(UPDATED_BY, '-IBO', '')) > 0

    SELECT @createdByUserKey = UserKey
      FROM UserMain
     WHERE UserId = @updatedByUserId

    SET @createdByUserKey = ISNULL(@createdByUserKey, @adminUserKey)

    -- make sure any reference data used is in the ref tables
    DECLARE @Prefixes TABLE (Prefix varchar(25))

    INSERT INTO @Prefixes
    SELECT DISTINCT a.PREFIX
      FROM inserted a left outer join PrefixRef b ON a.PREFIX = b.PrefixCode
     WHERE b.PrefixCode IS NULL

    INSERT INTO PrefixRef (PrefixCode, PrefixDesc, SyncPrefixCode, PrefixKey)
    SELECT Prefix, Prefix, Prefix, newid()
      FROM @Prefixes

    DECLARE @Suffixes TABLE (Suffix varchar(10))

    INSERT INTO @Suffixes
    SELECT DISTINCT a.SUFFIX
      FROM inserted a left outer join SuffixRef b ON a.SUFFIX = b.SuffixCode
     WHERE b.SuffixCode IS NULL

    INSERT INTO SuffixRef (SuffixCode, SuffixDesc, SyncSuffixCode, SuffixKey)
    SELECT Suffix, Suffix, Suffix, newid()
      FROM @Suffixes

    -- create the temp table variables we'll be using
    DECLARE @UpdateIDs TABLE (ID varchar(10), IsInstitute bit, ContactKey uniqueidentifier, PRIMARY KEY (ID, IsInstitute))

    DECLARE @InsertKeyMap TABLE (
        ID varchar(10),
        IsInstitute bit,
        ContactKey uniqueidentifier,
        GroupKey uniqueidentifier,
        AccessKey uniqueidentifier,
        OwnerAccessKey uniqueidentifier,
        GroupMemberKey uniqueidentifier,
        IsPrimary bit,
        PRIMARY KEY (ID, IsInstitute))

    -- Before we do anything else, handle any Member Type changes by updating the Member Type Groups accordingly
    IF EXISTS (SELECT 1 FROM inserted i INNER JOIN deleted d on i.[ID] = d.[ID] WHERE i.[MEMBER_TYPE] <> d.[MEMBER_TYPE])
    BEGIN
        -- remove from deleted member type security group
        DELETE gm
          FROM inserted i
               INNER JOIN deleted d ON i.[ID] = d.[ID]
               INNER JOIN [dbo].[ContactMain] cm ON d.ID = cm.SyncContactID
               INNER JOIN [dbo].[Member_Types] mt ON mt.[MEMBER_TYPE] = d.[MEMBER_TYPE]
               INNER JOIN [dbo].[GroupTypeRef] gt ON gt.[GroupTypeName] = 'Member Type Security'
               INNER JOIN [dbo].[GroupMain] g ON g.[GroupTypeKey] = gt.[GroupTypeKey] AND g.[Name] = mt.[DESCRIPTION]
               INNER JOIN [dbo].[GroupMember] gm ON gm.[MemberContactKey] = cm.ContactKey
         WHERE i.[MEMBER_TYPE] <> d.[MEMBER_TYPE] AND gm.[GroupKey] = g.[GroupKey]

        -- add to inserted member type security group
        INSERT INTO [dbo].[GroupMember] ([GroupMemberKey], [GroupKey], [MemberContactKey], [IsActive],
                                         [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn],
                                         [DropDate], [JoinDate], [MarkedForDeleteOn])
            SELECT NEWID(), g.GroupKey, u.UserKey, 1, @adminUserKey, @now, @adminUserKey, @now, NULL, NULL, NULL  
              FROM inserted i
                   INNER JOIN deleted d ON i.[ID] = d.[ID]
                   INNER JOIN [dbo].[ContactMain] cm ON i.[ID] = cm.[SyncContactID]
                   INNER JOIN [dbo].[UserMain] u ON u.[UserKey] = cm.[ContactKey]
                   INNER JOIN [dbo].[Member_Types] mt ON mt.[MEMBER_TYPE] = i.[MEMBER_TYPE]
                   INNER JOIN [dbo].[GroupTypeRef] gt ON gt.[GroupTypeName] = 'Member Type Security'
                   INNER JOIN [dbo].[GroupMain] g ON g.[GroupTypeKey] = gt.[GroupTypeKey] AND g.[Name] = mt.[DESCRIPTION]
             WHERE i.[MEMBER_TYPE] <> d.[MEMBER_TYPE]
                   AND NOT EXISTS (SELECT 1 FROM [dbo].[GroupMember] gm WHERE gm.[GroupKey] = g.[GroupKey] AND gm.[MemberContactKey] = cm.[ContactKey])
    END

    -- create the list of contacts to be updated (i.e., they exist in ContactMain already)
    INSERT INTO @UpdateIDs (ID, IsInstitute, ContactKey)
    SELECT a.ID, a.COMPANY_RECORD, b.ContactKey
      FROM inserted a inner join ContactMain b
        ON a.ID = b.ID
     WHERE b.IsInstitute = 1

    INSERT INTO @UpdateIDs (ID, IsInstitute, ContactKey)
    SELECT a.ID, a.COMPANY_RECORD, b.ContactKey
      FROM inserted a
           INNER JOIN ContactMain b ON a.ID = b.ID
           LEFT OUTER JOIN @UpdateIDs c ON a.ID = c.ID
     WHERE b.IsInstitute = 0
           AND c.ID IS NULL

    -- if the record is changing from Individual to Insititute and LAST_FIRST is not set
    -- 1. Change the UniformType in UniformRegistry
    -- 2. Change the IsInstitute flag in ContactMain
    -- 3. Delete the individual record
    -- 4. Delete the Salutations for the individual
    -- 5. Add the institute record, group, access, salutations, etc
    UPDATE a
       SET ComponentKey = b.ComponentKey
      FROM UniformRegistry a
           INNER JOIN ContactMain c ON a.UniformKey = c.ContactKey
           INNER JOIN inserted d ON c.ID = d.ID
           INNER JOIN deleted e ON c.ID = e.ID
           LEFT OUTER JOIN Institute f ON c.ContactKey = f.ContactKey, ComponentRegistry b
     WHERE b.Name = 'Institute'
           AND b.InterfaceName = 'BusinessController'
           AND f.ContactKey IS NULL
           AND ISNULL(d.LAST_FIRST, '') = ''    

    -- step 2
    UPDATE a
       SET IsInstitute = 1
      FROM ContactMain a
           INNER JOIN inserted b ON a.ID = b.ID
           INNER JOIN deleted c ON a.ID = c.ID
           LEFT OUTER JOIN Institute d ON a.ContactKey = d.ContactKey
     WHERE d.ContactKey IS NULL
           AND ISNULL(b.LAST_FIRST, '') = ''    

    -- step 3
    DELETE a
      FROM Individual a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
           INNER JOIN deleted d ON b.ID = d.ID
           LEFT OUTER JOIN Institute e ON a.ContactKey = e.ContactKey
     WHERE e.ContactKey IS NULL
       AND ISNULL(c.LAST_FIRST, '') = ''    

    -- step 4
    DELETE a
      FROM ContactSalutation a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
           INNER JOIN deleted d ON b.ID = d.ID
           LEFT OUTER JOIN Institute e ON b.ContactKey = e.ContactKey
     WHERE e.ContactKey IS NULL
       AND ISNULL(c.LAST_FIRST, '') = ''    

    -- step 5
    INSERT INTO @InsertKeyMap (ID, IsInstitute, ContactKey, GroupKey, AccessKey, OwnerAccessKey, GroupMemberKey, IsPrimary)
    SELECT b.ID, 1, a.ContactKey, newid(), newid(), newid(), newid(), 0
      FROM ContactMain a
           INNER JOIN inserted b ON a.ID = b.ID
           INNER JOIN deleted c ON a.ID = c.ID
           LEFT OUTER JOIN Institute d ON a.ContactKey = d.ContactKey
     WHERE ISNULL(b.LAST_FIRST, '') = ''    
       AND d.ContactKey IS NULL

    -- if the record is changing from Individual to Insititute, but still have first and last name, we have to:
    -- 1. Create a new Institute record with all the security and group stuff
    -- 2. Change the existing Individual record to have a P in the front of the ID
    -- 3. Add the old individual record to the new Institute group as the primary contact (this is at the end of the trigger)
    INSERT INTO @InsertKeyMap (ID, IsInstitute, ContactKey, GroupKey, AccessKey, OwnerAccessKey, GroupMemberKey, IsPrimary)
    SELECT b.ID, 1, newid(), newid(), newid(), newid(), newid(), 0
      FROM ContactMain a inner join inserted b
        ON a.ID = b.ID
     WHERE b.COMPANY_RECORD = 1
           AND a.IsInstitute = 0
           AND ISNULL(b.LAST_FIRST, '') <> ''

    -- step 2
    UPDATE a
       SET ID = 'P' + a.ID
      FROM ContactMain a inner join inserted b
        ON a.ID = b.ID
     WHERE b.COMPANY_RECORD = 1
          AND a.IsInstitute = 0
          AND ISNULL(b.LAST_FIRST, '') <> ''    

    -- If first and last name is being added to an existing Company record
    -- 1. Create a new Individual record, using P + ID
    -- 2. Make the individual the primary contact for the company

    INSERT INTO @InsertKeyMap (ID, IsInstitute, ContactKey, GroupKey, AccessKey, OwnerAccessKey, GroupMemberKey, IsPrimary)
    SELECT a.ID, 0, newid(), null, null, null, newid(), 1
      FROM inserted a
           LEFT OUTER JOIN ContactMain b ON a.ID = b.SyncContactID AND a.COMPANY_RECORD = 1 AND b.IsInstitute = 0
           INNER JOIN deleted c ON a.ID = c.ID
     WHERE b.SyncContactID IS NULL
           AND ISNULL(a.LAST_FIRST, '') <> ''
           AND ISNULL(c.LAST_FIRST, '') = ''
           AND a.COMPANY_RECORD = 1
           AND c.COMPANY_RECORD = 1

    -- If a Company record is changed to an Individual record
    -- 1. Change the UniformType in UniformRegistry
    -- 2. Delete the Group, GroupMember, GroupMemberDetail, Access stuff, and old salutations
    -- 3. Delete the institute record
    -- 4. Delete the P style Individual record, and all of its stuff, if one exists
    -- 5. Add the individual record, salutations,etc.
    -- 6. Change the IsInstitute flag in ContactMain
    UPDATE a
       SET ComponentKey = b.ComponentKey
      FROM UniformRegistry a
           INNER JOIN ContactMain c ON a.UniformKey = c.ContactKey
           INNER JOIN inserted d ON c.ID = d.ID, ComponentRegistry b
     WHERE b.Name = 'Individual'
           AND b.InterfaceName = 'BusinessController'
           AND d.COMPANY_RECORD = 0
           AND c.IsInstitute = 1
           AND ISNULL(d.LAST_FIRST, '') <> ''

    -- step 2
    -- delete the institute's group's Access records
    DECLARE @DeleteAccessKey TABLE (AccessKey uniqueidentifier)

    INSERT INTO @DeleteAccessKey
    SELECT e.AccessKey
      FROM Institute a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
           INNER JOIN GroupMain e ON a.InstituteGroupKey = e.GroupKey
     WHERE c.COMPANY_RECORD = 0
           AND b.IsInstitute = 1
           AND ISNULL(c.LAST_FIRST, '') <> ''
     UNION
    SELECT e.OwnerAccessKey
      FROM Institute a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
           INNER JOIN GroupMain e ON a.InstituteGroupKey = e.GroupKey
     WHERE c.COMPANY_RECORD = 0
           AND b.IsInstitute = 1
           AND ISNULL(c.LAST_FIRST, '') <> ''

    DECLARE @DeleteGroups TABLE (GroupKey uniqueidentifier)

    INSERT INTO @DeleteGroups
    SELECT a.InstituteGroupKey
      FROM Institute a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
     WHERE c.COMPANY_RECORD = 0
           AND b.IsInstitute = 1
           AND ISNULL(c.LAST_FIRST, '') <> ''

    -- delete the institute's group member details
    DELETE a
      FROM GroupMemberDetail a inner join GroupMember b
        ON a.GroupMemberKey = b.GroupMemberKey
           INNER JOIN @DeleteGroups c ON b.GroupKey = c.GroupKey

    -- delete the institute's group members
    DELETE a
      FROM GroupMember a
           INNER JOIN @DeleteGroups b ON a.GroupKey = b.GroupKey

    -- delete the institute's salutations
    DELETE a
      FROM ContactSalutation a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
     WHERE c.COMPANY_RECORD = 0
           AND b.IsInstitute = 1
           AND ISNULL(c.LAST_FIRST, '') <> ''

    -- step 3
    UPDATE e
       SET PrimaryInstituteContactKey = NULL
      FROM Institute a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
           INNER JOIN deleted d ON b.ID = d.ID
           INNER JOIN Individual e ON e.PrimaryInstituteContactKey = a.ContactKey
     WHERE c.COMPANY_RECORD = 0
           AND b.IsInstitute = 1
           AND ISNULL(c.LAST_FIRST, '') <> ''

    DELETE a
      FROM Institute a
           INNER JOIN ContactMain b ON a.ContactKey = b.ContactKey
           INNER JOIN inserted c ON b.ID = c.ID
           INNER JOIN deleted d ON b.ID = d.ID
     WHERE c.COMPANY_RECORD = 0
           AND b.IsInstitute = 1
           AND ISNULL(c.LAST_FIRST, '') <> ''

    -- now, delete the groups and the security
    UPDATE a
       SET GroupKey = NULL
      FROM AccessItem a inner join @DeleteGroups b
        ON a.GroupKey = b.GroupKey

    DELETE a
      FROM GroupMain a inner join @DeleteGroups b
        ON a.GroupKey = b.GroupKey

    DELETE a
      FROM UniformRegistry a inner join @DeleteGroups b
        ON a.UniformKey = b.GroupKey

    DELETE a
      FROM AccessItem a inner join @DeleteAccessKey b
        ON a.AccessKey = b.AccessKey

    DELETE a
      FROM AccessMain a inner join @DeleteAccessKey b
        ON a.AccessKey = b.AccessKey

    -- step 4
    UPDATE a
       SET ContactStatusCode = d.ContactStatusCode,
           UpdatedByUserKey = @adminUserKey,
           UpdatedOn = @now,
           SyncContactID = NULL,  -- Have to remove SyncContactID and ID since C/S can reuse IDs
                ID = NULL
      FROM ContactMain a
           inner join inserted b
        ON a.SyncContactID = b.ID
       AND a.ID <> b.ID
           inner join ContactStatusRef d
        ON d.ContactStatusDesc = 'Delete' AND d.IsSystem = 1
     WHERE b.COMPANY_RECORD = 0
       AND a.IsInstitute = 1
       AND ISNULL(b.LAST_FIRST, '') <> ''

    -- step 5
    INSERT INTO @InsertKeyMap (ID, IsInstitute, ContactKey, GroupKey, AccessKey, OwnerAccessKey, GroupMemberKey, IsPrimary)
    SELECT b.ID, 0, a.ContactKey, newid(), newid(), newid(), newid(), 0
      FROM ContactMain a inner join inserted b
        ON a.ID = b.ID
           inner join deleted c
        ON a.ID = c.ID
     WHERE b.COMPANY_RECORD = 0
       AND a.IsInstitute = 1
       AND ISNULL(b.LAST_FIRST, '') <> ''

    -- step 6
    UPDATE a
       SET IsInstitute = 0
      FROM ContactMain a inner join inserted b
        ON a.ID = b.ID
     WHERE b.COMPANY_RECORD = 0
       AND a.IsInstitute = 1
       AND ISNULL(b.LAST_FIRST, '') <> ''

    -- If the first and last name are removed from an existing Company record
    -- 1. Set the PrimaryContact on the Institute to null
    -- 2. Delete the Individual, Salutations, GroupMember, and GroupMemberDetail records for the Individual
    -- 3. Mark the ContactMain for the individual for delete
    UPDATE d
       SET PrimaryContactKey = NULL
      FROM ContactMain a inner join inserted b
        ON a.SyncContactID = b.ID
           inner join deleted c
        ON a.SyncContactID = c.ID
           inner join Institute d
        ON a.ContactKey = d.ContactKey
     WHERE b.COMPANY_RECORD = 1
       AND c.COMPANY_RECORD = 1
       AND a.IsInstitute = 1
       AND ISNULL(b.LAST_FIRST, '') = ''
       AND ISNULL(c.LAST_FIRST, '') <> ''

    DELETE e
      FROM ContactMain a inner join inserted b
        ON a.SyncContactID = b.ID
           inner join deleted c
        ON a.SyncContactID = c.ID
           inner join GroupMember d
        ON a.ContactKey = d.MemberContactKey
           inner join GroupMemberDetail e
        ON d.GroupMemberKey = e.GroupMemberKey
     WHERE b.COMPANY_RECORD = 1
       AND c.COMPANY_RECORD = 1
       AND a.IsInstitute = 0
       AND ISNULL(b.LAST_FIRST, '') = ''
       AND ISNULL(c.LAST_FIRST, '') <> ''

    DELETE d
      FROM ContactMain a inner join inserted b
        ON a.SyncContactID = b.ID
           inner join deleted c
        ON a.SyncContactID = c.ID
           inner join GroupMember d
        ON a.ContactKey = d.MemberContactKey
     WHERE b.COMPANY_RECORD = 1
       AND c.COMPANY_RECORD = 1
       AND a.IsInstitute = 0
       AND ISNULL(b.LAST_FIRST, '') = ''
       AND ISNULL(c.LAST_FIRST, '') <> ''

    DELETE d
      FROM ContactMain a inner join inserted b
        ON a.SyncContactID = b.ID
           inner join deleted c
        ON a.SyncContactID = c.ID
           inner join Individual d
        ON a.ContactKey = d.ContactKey
     WHERE b.COMPANY_RECORD = 1
       AND c.COMPANY_RECORD = 1
       AND a.IsInstitute = 0
       AND ISNULL(b.LAST_FIRST, '') = ''
       AND ISNULL(c.LAST_FIRST, '') <> ''

    DELETE d
      FROM ContactMain a inner join inserted b
        ON a.SyncContactID = b.ID
           inner join deleted c
        ON a.SyncContactID = c.ID
           inner join ContactSalutation d
        ON a.ContactKey = d.ContactKey
     WHERE b.COMPANY_RECORD = 1
       AND c.COMPANY_RECORD = 1
       AND a.IsInstitute = 0
       AND ISNULL(b.LAST_FIRST, '') = ''
       AND ISNULL(c.LAST_FIRST, '') <> ''

    UPDATE a
       SET ContactStatusCode = d.ContactStatusCode,
           UpdatedByUserKey = @adminUserKey,
           UpdatedOn = @now,
           SyncContactID = NULL,  -- Have to remove SyncContactID and ID since C/S can reuse IDs
                ID = NULL
       FROM ContactMain a inner join inserted b
         ON a.SyncContactID = b.ID
            inner join deleted c
         ON a.SyncContactID = c.ID
            inner join ContactStatusRef d
         ON d.ContactStatusDesc = 'Delete' AND d.IsSystem = 1
     WHERE b.COMPANY_RECORD = 1
       AND c.COMPANY_RECORD = 1
       AND a.IsInstitute = 0
       AND ISNULL(b.LAST_FIRST, '') = ''
       AND ISNULL(c.LAST_FIRST, '') <> ''

    /******************* This section for INSERTING into the .NET tables ***********************/

    -- create the keys to use for inserting the contacts, groups, etc. for records that don't already exist in ContactMain
    INSERT INTO @InsertKeyMap (ID, IsInstitute, ContactKey, GroupKey, AccessKey, OwnerAccessKey, GroupMemberKey, IsPrimary)
    SELECT a.ID, a.COMPANY_RECORD, ISNULL(c.UserKey, newid()), newid(), newid(), newid(), newid(), 0
      FROM inserted a left outer join ContactMain b
        ON a.ID = b.SyncContactID
       --AND a.COMPANY_RECORD = b.IsInstitute
           left outer join UserMain c
        ON a.ID = c.ContactMaster
     WHERE b.SyncContactID IS NULL
     
    -- fix up the problem records where a contact record is actually a company.  i.e., it has a company name
    -- but no contact name yet is marked as a contact rather than a company
    UPDATE a
       SET a.IsInstitute = 1
      FROM @InsertKeyMap a INNER JOIN inserted b ON a.ID = b.ID
     WHERE b.COMPANY_RECORD = 0
       AND LTRIM(RTRIM(ISNULL(b.LAST_FIRST, ''))) = ''
       AND LTRIM(RTRIM(ISNULL(b.COMPANY, ''))) <> ''

    -- then add in additional rows where the Name record represents both a company AND a contact
    -- Also - aded a check to not build the extra contact if there is already a contact with the same name
    -- attached to the company.  Not the best solution, but then we don't have great data in this case.
    INSERT INTO @InsertKeyMap (ID, IsInstitute, ContactKey, GroupKey, AccessKey, OwnerAccessKey, GroupMemberKey, IsPrimary)
    SELECT a.ID, 0, newid(), null, null, null, newid(), 1
      FROM inserted a left outer join ContactMain b
        ON a.ID = b.SyncContactID
       AND a.COMPANY_RECORD = 1
       AND b.IsInstitute = 0
           inner join @InsertKeyMap c
        ON a.ID = c.ID
           left outer join deleted d
        ON a.ID = d.ID
     WHERE b.SyncContactID IS NULL
       AND ISNULL(a.LAST_FIRST, '') <> ''
       AND ISNULL(d.LAST_FIRST, '') <> ''
       AND a.COMPANY_RECORD = 1
       AND d.COMPANY_RECORD = 1

    -- create the UniformRegistry entries for the individual records
    INSERT UniformRegistry (UniformKey, ComponentKey)
    SELECT a.ContactKey, c.ComponentKey
      FROM @InsertKeyMap a left outer join UniformRegistry b
        ON a.ContactKey = b.UniformKey, ComponentRegistry c
     WHERE b.UniformKey IS NULL
       AND a.IsInstitute = 0
       AND c.Name = 'Individual'
       AND c.InterfaceName = 'BusinessController'
    
    -- create the UniformRegistry entries for the company records
    INSERT UniformRegistry (UniformKey, ComponentKey)
    SELECT a.ContactKey, c.ComponentKey
      FROM @InsertKeyMap a left outer join UniformRegistry b
        ON a.ContactKey = b.UniformKey, ComponentRegistry c
     WHERE b.UniformKey IS NULL
       AND a.IsInstitute = 1
       AND c.Name = 'Institute'
       AND c.InterfaceName = 'BusinessController'
    
    -- create the UniformRegistry entries for the company Group records
    INSERT UniformRegistry (UniformKey, ComponentKey)
    SELECT a.GroupKey, b.ComponentKey
      FROM @InsertKeyMap a, ComponentRegistry b
     WHERE a.IsInstitute = 1
       AND b.Name = 'Group'
       AND b.InterfaceName = 'BusinessController'

    -- Create the group access sets for the company records
    INSERT AccessMain (AccessKey, AccessScope, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    SELECT AccessKey, 'Local', @createdByUserKey, @now, @createdByUserKey, @now
      FROM @InsertKeyMap
     WHERE IsInstitute = 1

    -- Grant Administrators role full control permission
    INSERT AccessItem (AccessKey, Grantee, Permission, RoleKey)
    SELECT AccessKey, @adminRoleKey, 1, @adminRoleKey
      FROM @InsertKeyMap
     WHERE IsInstitute = 1

    -- Grant Everyone role read permission
    INSERT AccessItem (AccessKey, Grantee, Permission, RoleKey)
    SELECT AccessKey, @everyoneRoleKey, 2, @everyoneRoleKey
      FROM @InsertKeyMap
     WHERE IsInstitute = 1
    
    -- Create the group owner access set (for items owned by the group)
    INSERT    AccessMain (AccessKey, AccessScope, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    SELECT    OwnerAccessKey, 'Local', @createdByUserKey, @now, @createdByUserKey, @now
      FROM @InsertKeyMap
     WHERE IsInstitute = 1

    -- Grant Administrators role full control permission
    INSERT AccessItem (AccessKey, Grantee, Permission, RoleKey)
    SELECT OwnerAccessKey, @adminRoleKey, 1, @adminRoleKey
      FROM @InsertKeyMap
     WHERE IsInstitute = 1

    -- Grant Everyone role read permission
    INSERT AccessItem (AccessKey, Grantee, Permission, RoleKey)
    SELECT OwnerAccessKey, @everyoneRoleKey, 2, @everyoneRoleKey
      FROM @InsertKeyMap
     WHERE IsInstitute = 1

    -- create the ContactMain entries for all
    INSERT INTO ContactMain
               (ContactKey, ContactStatusCode, FullName, SortName, IsInstitute, NoSolicitationFlag,
                SyncContactID, ID, IsIDEditable, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, PreferredAddressCategoryCode,
                IsSortNameOverridden, AccessKey, TextOnlyEmailFlag, ContactTypeKey, OptOutFlag)
    SELECT b.ContactKey,
           CASE WHEN a.STATUS like 'A%' THEN '1'
                WHEN a.STATUS like 'I%' THEN '2'
                WHEN a.STATUS like 'D%' THEN '3'
                WHEN a.STATUS like 'S%' THEN '4'
                ELSE '1' END,
           CASE WHEN b.IsInstitute = 0 THEN a.FULL_NAME ELSE a.COMPANY END,
           CASE WHEN b.IsInstitute = 1 AND DATALENGTH(a.COMPANY_SORT) > 0 THEN a.COMPANY_SORT ELSE a.LAST_FIRST END,
           b.IsInstitute,
           a.EXCLUDE_MAIL,
           a.ID,
           CASE WHEN b.IsInstitute = 0 AND b.IsPrimary = 1 THEN 'P' + a.ID ELSE a.ID END,
           0,
           @createdByUserKey,
           @now,
           @createdByUserKey,
           @now,
           '1',
           0,
           @accessKey,
           0,
           CASE WHEN b.IsInstitute = 0 THEN @individualTypeKey ELSE @instituteTypeKey END,
           0           
      FROM inserted a inner join @InsertKeyMap b on a.ID = b.ID LEFT OUTER JOIN ContactMain c
        ON b.ContactKey = c.ContactKey
     WHERE c.ContactKey IS NULL

    -- Create a new group for each company
    INSERT INTO [GroupMain]
           (GroupKey, [Name], Description, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn,
            IsSystem, IsAutoGenerated, GroupTypeKey, AccessKey, SystemEntityKey, IsInvitationOnly,
            GroupStatusCode, IsSimpleGroup, InheritRolesFlag, IsSingleRole, OwnerAccessKey)
    SELECT b.GroupKey, LEFT(a.COMPANY,65), 'Group for ' + a.COMPANY + ' Company', @createdByUserKey, @now, @createdByUserKey, @now,
            0, 1, @groupTypeKey, b.AccessKey, @systemEntityKey, c.IsInvitationOnly, 'A', c.IsSimpleGroup,
            c.InheritRolesFlag, c.IsSingleRole, b.OwnerAccessKey
      FROM inserted a inner join @InsertKeyMap b on a.ID = b.ID, GroupTypeRef c
     WHERE b.IsInstitute = 1
       AND c.GroupTypeKey = @groupTypeKey

    -- Give the groups full control to items protected by their owner access keys
    INSERT AccessItem (AccessKey, Grantee, Permission, GroupKey)
    SELECT OwnerAccessKey, GroupKey, 1, GroupKey
      FROM @InsertKeyMap
     WHERE IsInstitute = 1

    -- Create an institute for each company
    INSERT INTO Institute (ContactKey, InstituteName, InstituteGroupKey)
    SELECT b.ContactKey, a.COMPANY, b.GroupKey
      FROM inserted a inner join @InsertKeyMap b on a.ID = b.ID
     WHERE b.IsInstitute = 1

    -- Create new Individual records for those that are not companies
    INSERT INTO Individual
           (ContactKey, PrefixCode, FirstName, MiddleName, LastName, SuffixCode, Designation, Informal,
            Gender, BirthDate, PrimaryInstituteContactKey, PrimaryInstituteName, PrimaryInstituteTitle)
    SELECT b.ContactKey, a.PREFIX, a.FIRST_NAME, a.MIDDLE_NAME, a.LAST_NAME, a.SUFFIX, a.DESIGNATION, a.INFORMAL,
           a.GENDER, a.BIRTH_DATE, c.ContactKey, a.COMPANY, a.TITLE
      FROM inserted a inner join @InsertKeyMap b on a.ID = b.ID
           left outer join ContactMain c on a.CO_ID = c.SyncContactID and c.IsInstitute = 1
     WHERE b.IsInstitute = 0

    -- Add the primary contacts into their company's group
    INSERT INTO GroupMember
           (GroupMemberKey, GroupKey, MemberContactKey, IsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    SELECT a.GroupMemberKey, c.InstituteGroupKey, a.ContactKey, 1, @createdByUserKey, @now, @createdByUserKey, @now
      FROM @InsertKeyMap a inner join ContactMain b
        ON a.ID = b.SyncContactID
           inner join Institute c
        ON b.ContactKey = c.ContactKey
     WHERE a.IsInstitute = 0
       AND a.IsPrimary = 1
    
    INSERT INTO GroupMemberDetail
           (GroupMemberDetailKey, GroupMemberKey, GroupKey, GroupRoleKey, EffectiveDate,
            CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, GroupMemberStatusCode, IsActive)
    SELECT newid(), a.GroupMemberKey, c.InstituteGroupKey, @primaryGroupRoleKey, @now,
            @createdByUserKey, @now, @createdByUserKey, @now, @primaryDefaultMemberStatusCode, 1
      FROM @InsertKeyMap a
           INNER JOIN ContactMain b ON a.ID = b.SyncContactID
           INNER JOIN Institute c ON b.ContactKey = c.ContactKey
     WHERE a.IsInstitute = 0
       AND a.IsPrimary = 1

    -- update the Institute to set the primary contact key
    UPDATE c
       SET PrimaryContactKey = a.ContactKey
      FROM @InsertKeyMap a inner join ContactMain b
        ON a.ID = b.SyncContactID
           inner join Institute c
        ON b.ContactKey = c.ContactKey
     WHERE a.IsInstitute = 0
       AND a.IsPrimary = 1

    -- Add the regular contacts into their company groups too, if available
    INSERT INTO GroupMember
           (GroupMemberKey, GroupKey, MemberContactKey, IsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    SELECT a.GroupMemberKey, c.InstituteGroupKey, a.ContactKey, 1, @createdByUserKey, @now, @createdByUserKey, @now
      FROM @InsertKeyMap a inner join Individual b
        ON a.ContactKey = b.ContactKey
           inner join Institute c
        ON b.PrimaryInstituteContactKey = c.ContactKey
     WHERE a.IsInstitute = 0
       AND a.IsPrimary = 0

    INSERT INTO GroupMemberDetail
           (GroupMemberDetailKey, GroupMemberKey, GroupKey, GroupRoleKey, EffectiveDate,
            CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, GroupMemberStatusCode, IsActive)
    SELECT newid(), a.GroupMemberKey, c.InstituteGroupKey, @groupRoleKey, @now,
            @createdByUserKey, @now, @createdByUserKey, @now, @defaultMemberStatusCode, 1
      FROM @InsertKeyMap a inner join Individual b
        ON a.ContactKey = b.ContactKey
           inner join Institute c
        ON b.PrimaryInstituteContactKey = c.ContactKey
     WHERE a.IsInstitute = 0
       AND a.IsPrimary = 0

    -- Handle the Salutations.  To make this fast when the salutation formulas haven't changed, hard
    -- code the formula work here IF nothing has changed
    DECLARE @salutationFormulaCount int, @canCreateHere bit

    SET @canCreateHere = 0

     SELECT @salutationFormulaCount = count(*)
       FROM SalutationRef
      WHERE AutoCreateFlag = 1

    IF @salutationFormulaCount = 4
    BEGIN
        SELECT @salutationFormulaCount = count(*)
          FROM SalutationRef
         WHERE (SalutationDesc = 'Formal' AND IndividualFormula = '{[PrefixCode] }{[FirstName] }[LastName]' AND InstituteFormula = '[InstituteName]' AND AutoCreateFlag = 1)
            OR (SalutationDesc = 'Reverse Order' AND IndividualFormula = '{[LastName], }[FirstName]' AND InstituteFormula = '[SortName]' AND AutoCreateFlag = 1)
            OR (SalutationDesc = 'Informal' AND IndividualFormula = '[FirstName]' AND InstituteFormula = '[InstituteName]' AND AutoCreateFlag = 1)
            OR (SalutationDesc = 'Company' AND IndividualFormula = '[FullName]<n>[PrimaryInstituteTitle]<n>[PrimaryInstituteName]<n>' AND InstituteFormula = '[InstituteName]' AND AutoCreateFlag = 1)

        IF @salutationFormulaCount = 4
        BEGIN
            SET @canCreateHere = 1

            -- Individual Formal
            INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            SELECT newid(), 0,
                   RTRIM( CASE WHEN len(b.PREFIX) > 0 THEN b.PREFIX + ' ' ELSE '' END +
                          CASE WHEN len(b.FIRST_NAME) > 0 THEN b.FIRST_NAME + ' ' ELSE '' END +
                          CASE WHEN len(b.LAST_NAME) > 0 THEN b.LAST_NAME ELSE '' END),
                   a.ContactKey, c.SalutationKey, 1
              FROM @InsertKeyMap a
                   INNER JOIN inserted b ON a.ID = b.ID,
                   SalutationRef c
             WHERE c.SalutationDesc = 'Formal'
                   AND a.IsInstitute = 0
            
            -- Individual Reverse Order
            INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            SELECT newid(), 0,
                   CASE WHEN len(b.LAST_NAME) > 0 THEN b.LAST_NAME ELSE '' END +
                   CASE WHEN len(b.LAST_NAME) > 0 AND len(b.FIRST_NAME) > 0 THEN ', ' ELSE '' END +
                   CASE WHEN len(b.FIRST_NAME) > 0 THEN b.FIRST_NAME ELSE '' END,
                   a.ContactKey, c.SalutationKey, 1
              FROM @InsertKeyMap a
                   INNER JOIN inserted b ON a.ID = b.ID,
                   SalutationRef c
             WHERE c.SalutationDesc = 'Reverse Order'
                   AND a.IsInstitute = 0
            
            -- Individual Informal
            INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            SELECT newid(), 0, b.FIRST_NAME, a.ContactKey, c.SalutationKey, 1
              FROM @InsertKeyMap a
                   INNER JOIN inserted b ON a.ID = b.ID,
                   SalutationRef c
             WHERE c.SalutationDesc = 'Informal'
                   AND a.IsInstitute = 0

            -- Individual Company
            INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            SELECT newid(), 0,
                   CASE WHEN len(b.FULL_NAME) > 0 THEN b.FULL_NAME + char(13) + char(10) ELSE '' END +
                   CASE WHEN len(b.TITLE) > 0 THEN b.TITLE + char(13) + char(10) ELSE '' END +
                   CASE WHEN len(b.COMPANY) > 0 THEN b.COMPANY + char(13) + char(10) ELSE '' END,
                   a.ContactKey, c.SalutationKey, 1
              FROM @InsertKeyMap a
                   INNER JOIN inserted b ON a.ID = b.ID,
                   SalutationRef c
             WHERE c.SalutationDesc = 'Company'
                   AND a.IsInstitute = 0
            
            -- Institute Formal, Informal, and Company
            INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            SELECT newid(), 0, b.COMPANY, a.ContactKey, c.SalutationKey, 1
              FROM @InsertKeyMap a
                   INNER JOIN inserted b ON a.ID = b.ID,
                   SalutationRef c
             WHERE c.SalutationDesc IN ('Formal', 'Informal', 'Company')
                   AND a.IsInstitute = 1

            -- Institute Reverse Order
            INSERT ContactSalutation (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            SELECT newid(), 0, b.COMPANY_SORT, a.ContactKey, c.SalutationKey, 1
              FROM @InsertKeyMap a
                   INNER JOIN inserted b ON a.ID = b.ID,
                   SalutationRef c
             WHERE c.SalutationDesc = 'Reverse Order'
                   AND a.IsInstitute = 1
        END
    END

    -- if we couldn't create using the hard coded method, have to loop thru and use the SP
    IF @canCreateHere = 0
    BEGIN
        DECLARE @contactKey uniqueidentifier

        DECLARE GetContactsForSalutations CURSOR FAST_FORWARD FOR
        SELECT ContactKey FROM @InsertKeyMap

        OPEN GetContactsForSalutations

        FETCH GetContactsForSalutations INTO @contactKey
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC asi_GenerateSalutations @contactKey
            FETCH GetContactsForSalutations INTO @contactKey
        END

        CLOSE GetContactsForSalutations
        DEALLOCATE GetContactsForSalutations
    END

    /******************* This section for UPDATING the .NET tables for existing records ***********************/
    -- Update ContactMain for institutes, individuals, and primary contacts
    UPDATE ContactMain
       SET ContactStatusCode = CASE WHEN b.STATUS like 'A%' THEN '1'
                                    WHEN b.STATUS like 'I%' THEN '2'
                                    WHEN b.STATUS like 'D%' THEN '3'
                                    WHEN b.STATUS like 'S%' THEN '4'
                                    ELSE '1' END,
           UpdatedByUserKey = @createdByUserKey,
           UpdatedOn = @now,
           FullName = CASE WHEN ContactMain.IsInstitute = 0 THEN b.FULL_NAME ELSE b.COMPANY END,
           SortName = CASE WHEN ContactMain.IsInstitute = 0 THEN b.LAST_FIRST ELSE b.COMPANY_SORT END,
           NoSolicitationFlag = b.EXCLUDE_MAIL
      FROM @UpdateIDs a
           INNER JOIN inserted b ON a.ID = b.ID
     WHERE ContactMain.ContactKey = a.ContactKey

    -- Update Individual records
    UPDATE Individual
       SET PrefixCode = c.PrefixCode,
           SuffixCode = d.SuffixCode,
           FirstName = b.FIRST_NAME,
           MiddleName = b.MIDDLE_NAME,
           LastName = b.LAST_NAME,
           Designation = b.DESIGNATION,
           Informal = b.INFORMAL,
           Gender = b.GENDER,
           BirthDate = b.BIRTH_DATE,
           PrimaryInstituteName = b.COMPANY,
           PrimaryInstituteTitle = b.TITLE,
           PrimaryInstituteContactKey = e.ContactKey
      FROM @UpdateIDs a
           INNER JOIN inserted b ON a.ID = b.ID
           LEFT OUTER JOIN PrefixRef c ON b.PREFIX = c.PrefixCode
           LEFT OUTER JOIN SuffixRef d ON b.SUFFIX = d.SuffixCode
           LEFT OUTER JOIN ContactMain e ON b.COMPANY = e.FullName AND e.IsInstitute = 1
     WHERE Individual.ContactKey = a.ContactKey

    -- Update company records
    UPDATE Institute
       SET InstituteName = b.COMPANY
      FROM @UpdateIDs a INNER JOIN inserted b ON a.ID = b.ID
     WHERE Institute.ContactKey = a.ContactKey
           AND Institute.InstituteName <> b.COMPANY

    -- If the record is changing from Individual to Insititute, but still have first and last name
    -- Step 3 from way above. Add the old individual record to the new Institutes group as the primary contact
    -- add the old individual record to the new Institutes group
    INSERT INTO GroupMember
           (GroupMemberKey, GroupKey, MemberContactKey, IsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    SELECT newid(), e.InstituteGroupKey, a.ContactKey, 1, @createdByUserKey, @now, @createdByUserKey, @now
      FROM ContactMain a
           INNER JOIN inserted b ON a.SyncContactID = b.ID
           INNER JOIN deleted c ON a.SyncContactID = c.ID
           INNER JOIN ContactMain d ON d.SyncContactID = c.ID
           INNER JOIN Institute e ON d.ContactKey = e.ContactKey
     WHERE b.COMPANY_RECORD = 1
           AND c.COMPANY_RECORD = 0
           AND ISNULL(b.LAST_FIRST, '') <> ''
           AND a.IsInstitute = 0
           AND d.IsInstitute = 1

    INSERT INTO GroupMemberDetail
           (GroupMemberDetailKey, GroupMemberKey, GroupKey, GroupRoleKey, EffectiveDate,
            CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, GroupMemberStatusCode, IsActive)
    SELECT newid(), f.GroupMemberKey, e.InstituteGroupKey, @groupRoleKey, @now,
            @createdByUserKey, @now, @createdByUserKey, @now, @defaultMemberStatusCode, 1
      FROM ContactMain a
           INNER JOIN inserted b ON a.SyncContactID = b.ID
           INNER JOIN deleted c ON a.SyncContactID = c.ID
           INNER JOIN ContactMain d ON d.SyncContactID = c.ID
           INNER JOIN Institute e ON d.ContactKey = e.ContactKey
           INNER JOIN GroupMember f ON e.InstituteGroupKey = f.GroupKey
     WHERE b.COMPANY_RECORD = 1
           AND c.COMPANY_RECORD = 0
           AND ISNULL(b.LAST_FIRST, '') <> ''
           AND a.IsInstitute = 0
           AND d.IsInstitute = 1

    -- set the primary contact on the new institute to the old individual
    UPDATE e
       SET PrimaryContactKey = a.ContactKey
      FROM ContactMain a
           INNER JOIN inserted b ON a.SyncContactID = b.ID
           INNER JOIN deleted c ON a.SyncContactID = c.ID
           INNER JOIN ContactMain d ON d.SyncContactID = c.ID
           INNER JOIN Institute e ON d.ContactKey = e.ContactKey
     WHERE b.COMPANY_RECORD = 1
           AND c.COMPANY_RECORD = 0
           AND ISNULL(b.LAST_FIRST, '') <> ''
           AND a.IsInstitute = 0
           AND d.IsInstitute = 1

    -- get rid of company contacts that WERE on the company record but are not anymore
    -- leave the contacts in the system, just disconnect them from the company
    DELETE h
      FROM @UpdateIDs a
           INNER JOIN inserted b on a.ID = b.ID
           INNER JOIN deleted c ON a.ID = c.ID
           INNER JOIN ContactMain d ON a.ID = d.SyncContactID AND d.IsInstitute = 0
           INNER JOIN ContactMain e ON a.ID = e.SyncContactID AND e.IsInstitute = 1
           INNER JOIN Institute f ON e.ContactKey = f.ContactKey
           INNER JOIN GroupMember g ON f.InstituteGroupKey = g.GroupKey AND d.ContactKey = g.MemberContactKey
           INNER JOIN GroupMemberDetail h ON g.GroupMemberKey = h.GroupMemberKey
     WHERE b.COMPANY_RECORD = 1
           AND LTRIM(RTRIM(ISNULL(b.LAST_FIRST, ''))) = ''
           AND LTRIM(RTRIM(ISNULL(c.LAST_FIRST, ''))) <> ''

    DELETE g
      FROM @UpdateIDs a
           INNER JOIN inserted b on a.ID = b.ID
           INNER JOIN deleted c ON a.ID = c.ID
           INNER JOIN ContactMain d ON a.ID = d.SyncContactID AND d.IsInstitute = 0
           INNER JOIN ContactMain e ON a.ID = e.SyncContactID AND e.IsInstitute = 1
           INNER JOIN Institute f ON e.ContactKey = f.ContactKey
           INNER JOIN GroupMember g ON f.InstituteGroupKey = g.GroupKey AND d.ContactKey = g.MemberContactKey
     WHERE b.COMPANY_RECORD = 1
           AND LTRIM(RTRIM(ISNULL(b.LAST_FIRST, ''))) = ''
           AND LTRIM(RTRIM(ISNULL(c.LAST_FIRST, ''))) <> ''

    -- Fix up the problem caused by converting a "CM" record with two ContactMain records
    -- to a "M" record, which leaves two Individual records associated with the same Name record
    DECLARE @ids TABLE (id varchar(10) NOT NULL)
    DECLARE @idCount int
    DECLARE @fixID varchar(10)
    INSERT INTO @ids (id)
        SELECT DISTINCT i.ID FROM inserted i INNER JOIN deleted d ON i.ID = d.ID
                                             INNER JOIN Member_Types m1 ON i.MEMBER_TYPE = m1.MEMBER_TYPE
                                             INNER JOIN Member_Types m2 ON d.MEMBER_TYPE = m2.MEMBER_TYPE
            WHERE (i.MEMBER_TYPE <> d.MEMBER_TYPE AND (m1.MEMBER_RECORD <> m2.MEMBER_RECORD OR m1.COMPANY_RECORD <> m2.COMPANY_RECORD))
                  OR i.COMPANY_RECORD <> d.COMPANY_RECORD OR i.MEMBER_RECORD <> d.MEMBER_RECORD
    SELECT @idCount = COUNT(1) FROM @ids    
    IF @idCount > 0
    BEGIN
        IF @idCount = 1
        BEGIN
            -- Optimization to avoid cursor in most common case
            SELECT @fixID = id FROM @ids
            EXEC [dbo].[asi_FixDuplicateIndividualRowsById] @fixID
        END
        ELSE
        BEGIN
            -- Handle batch operation
            DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
                SELECT id FROM @ids
            
            OPEN c
            FETCH NEXT FROM c INTO @fixID
            WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC [dbo].[asi_FixDuplicateIndividualRowsById] @fixID
                FETCH NEXT FROM c INTO @fixID
            END
        END
    END
        
    SET NOCOUNT OFF
END




GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameBT_ID] ON [dbo].[Name] ([BT_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameCO_ID] ON [dbo].[Name] ([CO_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_COMPANY] ON [dbo].[Name] ([COMPANY]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameCOMPANY_SORT] ON [dbo].[Name] ([COMPANY_SORT]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameDUP_MATCH_KEY] ON [dbo].[Name] ([DUP_MATCH_KEY]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameEMAIL] ON [dbo].[Name] ([EMAIL]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_FIRST_NAME_LAST_NAME] ON [dbo].[Name] ([FIRST_NAME], [LAST_NAME]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_FULL_NAME] ON [dbo].[Name] ([FULL_NAME]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameLAST_FIRST] ON [dbo].[Name] ([LAST_FIRST]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_LAST_NAME_FIRST_NAME] ON [dbo].[Name] ([LAST_NAME], [FIRST_NAME]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameMAIL_ADDRESS_NUM] ON [dbo].[Name] ([MAIL_ADDRESS_NUM]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameMAJOR_KEY] ON [dbo].[Name] ([MAJOR_KEY]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iNameMEMBER_TYPE] ON [dbo].[Name] ([MEMBER_TYPE]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Name] TO [IMIS]
GRANT SELECT ON  [dbo].[Name] TO [IMIS]
GRANT INSERT ON  [dbo].[Name] TO [IMIS]
GRANT DELETE ON  [dbo].[Name] TO [IMIS]
GRANT UPDATE ON  [dbo].[Name] TO [IMIS]
GO